<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1" /><title>DB2Translator xref</title>
<link type="text/css" rel="stylesheet" href="../../../../../stylesheet.css" />
</head>
<body>
<div id="overview"><a href="../../../../../../../../../../../../../../apidocs/org/sakaiproject/genericdao/springjdbc/translators/DB2Translator.html">View Javadoc</a></div><pre>

<a name="1" href="#1">1</a>   <em>/**<em>*</em></em>
<a name="2" href="#2">2</a>   <em> * $Id: DB2Translator.html 11 2009-01-29 14:58:33Z azeckoski $</em>
<a name="3" href="#3">3</a>   <em> * $URL: http://genericdao.googlecode.com/svn/maven/site/xref/org/sakaiproject/genericdao/springjdbc/translators/DB2Translator.html $</em>
<a name="4" href="#4">4</a>   <em> * DB2Translator.java - genericdao - Apr 26, 2008 2:29:28 PM - azeckoski</em>
<a name="5" href="#5">5</a>   <em> **************************************************************************</em>
<a name="6" href="#6">6</a>   <em> * Copyright (c) 2008 Aaron Zeckoski</em>
<a name="7" href="#7">7</a>   <em> * Licensed under the Apache License, Version 2</em>
<a name="8" href="#8">8</a>   <em> * </em>
<a name="9" href="#9">9</a>   <em> * A copy of the Apache License, Version 2 has been included in this </em>
<a name="10" href="#10">10</a>  <em> * distribution and is available at: <a href="http://www.apache.org/licenses/LICENSE-2.0.txt" target="alexandria_uri">http://www.apache.org/licenses/LICENSE-2.0.txt</a></em>
<a name="11" href="#11">11</a>  <em> *</em>
<a name="12" href="#12">12</a>  <em> * Aaron Zeckoski (azeckoski@gmail.com) (aaronz@vt.edu) (aaron@caret.cam.ac.uk)</em>
<a name="13" href="#13">13</a>  <em> */</em>
<a name="14" href="#14">14</a>  
<a name="15" href="#15">15</a>  <strong>package</strong> org.sakaiproject.genericdao.springjdbc.translators;
<a name="16" href="#16">16</a>  
<a name="17" href="#17">17</a>  
<a name="18" href="#18">18</a>  <em>/**<em>*</em></em>
<a name="19" href="#19">19</a>  <em> * IBM DB2 database translator</em>
<a name="20" href="#20">20</a>  <em> * </em>
<a name="21" href="#21">21</a>  <em> * @author Aaron Zeckoski (azeckoski@gmail.com)</em>
<a name="22" href="#22">22</a>  <em> */</em>
<a name="23" href="#23">23</a>  <strong>public</strong> <strong>class</strong> DB2Translator <strong>extends</strong> <a href="../../../../../org/sakaiproject/genericdao/springjdbc/translators/BasicTranslator.html">BasicTranslator</a> {
<a name="24" href="#24">24</a>  
<a name="25" href="#25">25</a>     <em class="comment">/*<em class="comment"> (non-Javadoc)</em></em>
<a name="26" href="#26">26</a>  <em class="comment">    * @see org.sakaiproject.genericdao.api.translators.DatabaseTranslator#handlesDB()</em>
<a name="27" href="#27">27</a>  <em class="comment">    */</em>
<a name="28" href="#28">28</a>     <strong>public</strong> String handlesDB() {
<a name="29" href="#29">29</a>        <strong>return</strong> DBTYPE_DB2;
<a name="30" href="#30">30</a>     }
<a name="31" href="#31">31</a>  
<a name="32" href="#32">32</a>     <em class="comment">/*<em class="comment"> (non-Javadoc)</em></em>
<a name="33" href="#33">33</a>  <em class="comment">    * @see org.sakaiproject.genericdao.api.translators.DatabaseTranslator#makeAutoIdQuery(java.lang.String)</em>
<a name="34" href="#34">34</a>  <em class="comment">    */</em>
<a name="35" href="#35">35</a>     <strong>public</strong> String makeAutoIdQuery(String tableName, String idColumnName) {
<a name="36" href="#36">36</a>        <strong>return</strong> <span class="string">"values identity_val_local()"</span>;
<a name="37" href="#37">37</a>     }
<a name="38" href="#38">38</a>  
<a name="39" href="#39">39</a>     <em class="comment">/*<em class="comment"> (non-Javadoc)</em></em>
<a name="40" href="#40">40</a>  <em class="comment">    * @see org.sakaiproject.genericdao.api.translators.DatabaseTranslator#makeLimitQuery(java.lang.String, long, long)</em>
<a name="41" href="#41">41</a>  <em class="comment">    */</em>
<a name="42" href="#42">42</a>     <strong>public</strong> String makeLimitQuery(String sql, <strong>long</strong> start, <strong>long</strong> limit, String tableName) {
<a name="43" href="#43">43</a>        <strong>int</strong> startOfSelect = sql.toLowerCase().indexOf(<span class="string">"select"</span>);
<a name="44" href="#44">44</a>  
<a name="45" href="#45">45</a>        StringBuilder pagingSelect = <strong>new</strong> StringBuilder( sql.length()+100 )
<a name="46" href="#46">46</a>                 .append( sql.substring(0, startOfSelect) ) <em class="comment">//add the comment</em>
<a name="47" href="#47">47</a>                 .append(<span class="string">"select * from ( select "</span>) <em class="comment">//nest the main query in an outer select</em>
<a name="48" href="#48">48</a>                 .append( getRowNumber(sql) ); <em class="comment">//add the rownnumber bit into the outer query select list</em>
<a name="49" href="#49">49</a>  
<a name="50" href="#50">50</a>        <strong>if</strong> ( hasDistinct(sql) ) {
<a name="51" href="#51">51</a>           pagingSelect.append(<span class="string">" row_.* from ( "</span>) <em class="comment">//add another (inner) nested select</em>
<a name="52" href="#52">52</a>              .append( sql.substring(startOfSelect) ) <em class="comment">//add the main query</em>
<a name="53" href="#53">53</a>              .append(<span class="string">" ) as row_"</span>); <em class="comment">//close off the inner nested select</em>
<a name="54" href="#54">54</a>        } <strong>else</strong> {
<a name="55" href="#55">55</a>           pagingSelect.append( sql.substring( startOfSelect + 6 ) ); <em class="comment">//add the main query</em>
<a name="56" href="#56">56</a>        }
<a name="57" href="#57">57</a>  
<a name="58" href="#58">58</a>        pagingSelect.append(<span class="string">" ) as temp_ where rownumber_ "</span>);
<a name="59" href="#59">59</a>  
<a name="60" href="#60">60</a>        <em class="comment">//add the restriction to the outer select</em>
<a name="61" href="#61">61</a>        <strong>if</strong> (start > 0) {
<a name="62" href="#62">62</a>           pagingSelect.append(<span class="string">"between ?+1 and ?"</span>);
<a name="63" href="#63">63</a>        } <strong>else</strong> {
<a name="64" href="#64">64</a>           pagingSelect.append(<span class="string">"&lt;= ?"</span>);
<a name="65" href="#65">65</a>        }
<a name="66" href="#66">66</a>        <strong>return</strong> pagingSelect.toString();
<a name="67" href="#67">67</a>     }
<a name="68" href="#68">68</a>  
<a name="69" href="#69">69</a>     <em>/**<em>*</em></em>
<a name="70" href="#70">70</a>  <em>    * true if this query is for a distinct result</em>
<a name="71" href="#71">71</a>  <em>    */</em>
<a name="72" href="#72">72</a>     <strong>private</strong> <strong>static</strong> <strong>boolean</strong> hasDistinct(String sql) {
<a name="73" href="#73">73</a>        <strong>return</strong> sql.toLowerCase().indexOf(<span class="string">"select distinct"</span>)>=0;
<a name="74" href="#74">74</a>     }
<a name="75" href="#75">75</a>  
<a name="76" href="#76">76</a>     <em>/**<em>*</em></em>
<a name="77" href="#77">77</a>  <em>    * Render the &lt;tt>rownumber() over ( .... ) as rownumber_,&lt;/tt></em>
<a name="78" href="#78">78</a>  <em>    * bit, that goes in the select list</em>
<a name="79" href="#79">79</a>  <em>    */</em>
<a name="80" href="#80">80</a>     <strong>private</strong> String getRowNumber(String sql) {
<a name="81" href="#81">81</a>        StringBuilder rownumber = <strong>new</strong> StringBuilder(50).append(<span class="string">"rownumber() over("</span>);
<a name="82" href="#82">82</a>  
<a name="83" href="#83">83</a>        <strong>int</strong> orderByIndex = sql.toLowerCase().indexOf(<span class="string">"order by"</span>);
<a name="84" href="#84">84</a>  
<a name="85" href="#85">85</a>        <strong>if</strong> ( orderByIndex>0 &amp;&amp; !hasDistinct(sql) ) {
<a name="86" href="#86">86</a>           rownumber.append( sql.substring(orderByIndex) );
<a name="87" href="#87">87</a>        }
<a name="88" href="#88">88</a>  
<a name="89" href="#89">89</a>        rownumber.append(<span class="string">") as rownumber_,"</span>);
<a name="90" href="#90">90</a>  
<a name="91" href="#91">91</a>        <strong>return</strong> rownumber.toString();
<a name="92" href="#92">92</a>     }
<a name="93" href="#93">93</a>  
<a name="94" href="#94">94</a>  }
</pre>
<hr/><div id="footer">This page was automatically generated by <a href="http://maven.apache.org/">Maven</a></div></body>
</html>

